package de.fhb.itunes.repository.DataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import de.fhb.itunes.repository.data.Album_VO;
import de.fhb.itunes.repository.data.Musik_VO;
import de.fhb.itunes.repository.jdbc.DataAccessException;
import de.fhb.itunes.repository.jdbc.JdbcDAOBase;

public class DAO_Album extends JdbcDAOBase implements IDAO_Album {

	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		Album_VO album = new Album_VO();
		album.setAlbuminterpret("");
		album.setAlbuminterpret(rs.getString("AlbumInterpret"));
		album.setAlbumname(rs.getString("Album"));
		// Holen der Titel von dem jeweiligen Album
		if (album.getAlbumname().length() != 0) {
			IDAO_Musik musikverwaltung = new DAO_Musik();
			album.setTitel(musikverwaltung.getbyAlbumAsHash(album
					.getAlbumname()));
		}
		return album;
	}
	protected Object mapAll2VO(ResultSet rs, Connection con) throws SQLException{
		Album_VO album = new Album_VO();
		album.setAlbuminterpret("");
		album.setAlbuminterpret(rs.getString("AlbumInterpret"));
		album.setAlbumname(rs.getString("Album"));
		// Holen der Titel von dem jeweiligen Album
		if (album.getAlbumname().length() != 0) {
			IDAO_Musik musikverwaltung = new DAO_Musik();
			album.setTitel(musikverwaltung.getbyAlbumAsHash(album
					.getAlbumname(),con));
		}
		return album;
	}

	private List transfomallalben(ResultSet rs, Connection con) throws SQLException{
		List voList = new LinkedList();
		Object vo;
		while (rs.next()) {
			vo = mapAll2VO(rs, con);
			voList.add(vo);
		}
		return voList;
	}
	@Override
	public List<Album_VO> getall() {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT Distinct Album , AlbumInterpret FROM  [itunes].[dbo].[MediaFileSatz] media,   [itunes].[dbo].[MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id";//"SELECT DISTINCT Album, AlbumInterpret FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();

			vos = transfomallalben(rs, con);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Album_VO> getbyAlbumname(String albumname) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT TOP 1 * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND musik.Album = ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, albumname);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public Album_VO getbyAlbumnameAndAlbumInterpet(String albumname,
			String albuminterpret) {
		IDAO_Musik musikverwaltung = new DAO_Musik();

		List<Musik_VO> musikliste = musikverwaltung
				.getbyAlbumAndAlbumInterpret(albumname, albuminterpret);
		Album_VO album = new Album_VO();
		album.setAlbuminterpret(musikliste.get(0).getAlbuminterpret());
		album.setAlbumname(musikliste.get(0).getAlbum());
		HashMap<Integer, Musik_VO> hashlist = new HashMap<Integer, Musik_VO>();
		for (Musik_VO musik : musikliste) {
			hashlist.put(musik.getTitelnummer(), musik);
		}
		album.setTitel(hashlist);

//		Album_VO vos;
//		Connection con = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//
//		String sqlStr = "SELECT  TOP 1 * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND musik.Album = ? AND musik.Albuminterpret = ?";
//		try {
//			con = getConnection();
//			ps = con.prepareStatement(sqlStr);
//			ps.setString(1, albumname);
//			ps.setString(2, albuminterpret);
//			rs = ps.executeQuery();
//
//			vos = (Album_VO) transformSetToVO(rs);
//		} catch (SQLException sqle) {
//			throw new DataAccessException("Error executing " + sqlStr, sqle);
//		} finally {
//			try {
//				// close the resources
//				if (ps != null)
//					ps.close();
//				if (con != null)
//					con.close();
//			} catch (SQLException sqlee) {
//				throw new DataAccessException("Can't close connection", sqlee);
//			}
//		}

		return album;
	}
	@Override
	public List<Album_VO> searchbyKeyword(String keyword) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT  * FROM  [MediaFileSatz] media,   [MediaFileSatz_Musiktitel] musik WHERE media.id = musik.id AND (musik.Album like ? or musik.Interpret like ?)";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + keyword + "%");
			ps.setString(2, "%" + keyword + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

}
